﻿Imports MICROFORMA.BEL
Imports MICROFORMA.DIL
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common
Imports System.Data

Public Class IndicesDA
    Implements IndicesDI

    Dim stringConnection As String = "Microforma.My.MySettings.Setting"
    Public Function Eliminar(ByVal IdIndice As Integer) As String Implements DIL.IndicesDI.Eliminar
        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)
        Dim query As String
        Try

            query = "delete from indices where idIndice=" & IdIndice

            If query = "" Then
                Return "OK"
            End If

            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)

            Return "OK"
        Catch ex As Exception
            Return ex.Message.ToString
        End Try
    End Function

    Public Function Update(ByVal indicesBE As BEL.IndicesBE) As String Implements DIL.IndicesDI.Update
        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)
        Dim query As String
        Try

            query = GetSqlUpdateIndice(indicesBE)

            If query = "" Then
                Return "OK"
            End If

            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)


            db.ExecuteNonQuery(o_dbCommand)

            Return "OK"
        Catch ex As Exception
            Return ex.Message.ToString
        End Try
    End Function
    Function GetSqlUpdateIndice(ByVal IndicesBE As IndicesBE) As String
        Dim StrCabecera As String
        StrCabecera = "Update indices set "

        If IndicesBE.ValueDefault Then
            If IndicesBE.Correlativy Then
                StrCabecera = StrCabecera & " CorrelativoValor=" & IndicesBE.CorrelativoValor & ","
                StrCabecera = StrCabecera & " CorrelativoSumar='" & IndicesBE.CorrelativoSumar & "',"
            Else
                StrCabecera = StrCabecera & " Prefijo='',CorrelativoValor=NULL,"
            End If
            If IndicesBE.QueryDB Then
                StrCabecera = StrCabecera & " ConsultaSQLExterna='" & IndicesBE.ConsultaSQLExterna & "',"
            Else
                StrCabecera = StrCabecera & " ConsultaSQLExterna='',"
            End If
        Else
            StrCabecera = StrCabecera & " Prefijo='',CorrelativoValor=NULL,ConsultaSQLExterna='',"
        End If

        Select Case IndicesBE.IdTipoDato
            Case 1 'Tipo Texto                
                If IndicesBE.PrefijoBol Then
                    StrCabecera = StrCabecera & " Prefijo='" & IndicesBE.Prefijo & "',"
                    StrCabecera = StrCabecera & " PrefijoAdelante='" & IndicesBE.PrefijoAdelante & "',"
                End If
                If IndicesBE.ValueDefault Then
                    If IndicesBE.CreateIndice Then
                        StrCabecera = StrCabecera & " CadenaVariables='" & IndicesBE.CadenaVariables & "',"
                    Else
                        StrCabecera = StrCabecera & " CadenaVariables='',"
                    End If
                Else
                    StrCabecera = StrCabecera & " CadenaVariables='',"
                End If
                StrCabecera = StrCabecera & " MascaraEntrada='" & IndicesBE.MascaraEntrada & "',"
            Case 2, 3 'numeros
                StrCabecera = StrCabecera & " RangoInicio=" & IndicesBE.RangoInicio & ","
                StrCabecera = StrCabecera & " RangoFin=" & IndicesBE.RangoFin & ","
            Case 4 'Fecha
                StrCabecera = StrCabecera & " MascaraEntrada='" & IndicesBE.MascaraEntrada & "',"
            Case 6, 7    'Combos
                StrCabecera = StrCabecera & " IdLista=" & IndicesBE.IdLista & ","
            Case 8 'Grilla
                Dim ItemGrilla As IndiceGrillaBE
                Dim StrIndicesGrilla As String
                For Each ItemGrilla In IndicesBE.IndicesGrillaCollection
                    StrIndicesGrilla += ItemGrilla.Indice & vbNewLine
                Next
                StrCabecera = StrCabecera & " IndicesGrilla='" & StrIndicesGrilla & "',"

        End Select

        StrCabecera = StrCabecera & " Duplicado='" & IndicesBE.Duplicado & "',"
        StrCabecera = StrCabecera & " Obligatorio='" & IndicesBE.Obligatorio & "',"
        StrCabecera = StrCabecera & " DobleDigitacion='" & IndicesBE.DobleDigitacion & "'"

        StrCabecera = StrCabecera & " where Nombre='" & IndicesBE.Nombre & "'"
        Return StrCabecera
    End Function
    Public Function Add(ByVal indicesBE As BEL.IndicesBE) As String Implements DIL.IndicesDI.Add
        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)
        Dim query As String
        Try

            Dim StrTipoDato As String = ""

            query = GetQueryIndices(indicesBE)
            query = query & " SELECT @@IDENTITY"

            Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)
            db.ExecuteNonQuery(o_dbCommand)

            'Crea indice en tabla perteneciente al tipo documental
            Select Case indicesBE.IdTipoDato
                Case 1, 6, 7 'Texto, lista, lista multiple
                    StrTipoDato = " varchar(200) "
                Case 2 'numero sin decimales
                    StrTipoDato = " integer "
                Case 3 'Numero con decimales
                    StrTipoDato = " [numeric] (18, 2) "
                Case 4 'Fecha hora
                    StrTipoDato = " datetime "
                Case 5 'Fecha hora
                    StrTipoDato = " bit "
            End Select

            If indicesBE.IdTipoDato = 8 Then 'Tipo de dato "GRILLA" virtual en tabla. 
                Return "OK"
            End If

            query = "Alter table " & indicesBE.NombreTipoDocumental.Trim.Replace(" ", "")
            query = query & " ADD " & indicesBE.NombreInterno & StrTipoDato
            o_dbCommand = db.GetSqlStringCommand(query)
            db.ExecuteNonQuery(o_dbCommand)


            Return "OK"

        Catch ex As Exception

            Return ex.Message.ToString

        End Try
    End Function

    Function SetTransaction(ByVal indicesBE As IndicesBE, ByVal o_dbCommand As DbCommand, ByVal db As Database) As String
        Dim idIndice As Int16 = 0

        Dim o_Cnx As DbConnection = db.CreateConnection()

        o_Cnx.Open()

        Dim o_Transaction As DbTransaction = o_Cnx.BeginTransaction

        Try

            idIndice = db.ExecuteScalar(o_dbCommand, o_Transaction)

            For i As Int16 = 0 To indicesBE.IndicesGrillaCollection.Count - 1

                indicesBE.IndicesGrillaCollection(i).IdGrilla = idIndice

                Dim query2 As String = GetQueryIndiceGrilla(indicesBE.IndicesGrillaCollection(i))

                db.ExecuteNonQuery(o_Transaction, CommandType.Text, query2)

            Next

            o_Transaction.Commit()

        Catch ex As Exception
            o_Transaction.Rollback()
            Return ex.Message.ToString
        Finally
            o_Cnx.Close()
        End Try

        Return "OK"
    End Function

    Function GetQueryIndices(ByVal IndicesBE As IndicesBE) As String
        Dim query As String


        query = "Insert into Indices (Nombre,IdTipoDocumental,NombreInterno,IdTipoDato,MascaraEntrada,TipoIndexacion,ZoomAutomatico, " _
                                & " Duplicado,Obligatorio,DobleDigitacion)" _
                                & "Values ('" & IndicesBE.Nombre & "'," & IndicesBE.IdTipoDocumental _
                                & ",'" & IndicesBE.NombreInterno & "','" & IndicesBE.IdTipoDato _
                                & "','" & IndicesBE.MascaraEntrada & "','" & IndicesBE.TipoIndexacion _
                                & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                                & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"


        If IndicesBE.IdTipoDato = 2 Or IndicesBE.IdTipoDato = 3 Then 'NUMERO CON O SIN DECIMALES
            query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,TipoIndexacion,ZoomAutomatico, " _
                                    & " Duplicado,Obligatorio,DobleDigitacion,RangoInicio,RangoFin)" _
                                    & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                    & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                                    & ",'" & IndicesBE.MascaraEntrada & "','" & IndicesBE.TipoIndexacion _
                                    & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                                    & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) _
                                    & "," & IIf(IndicesBE.RangoInicio.Trim = "", 0, IndicesBE.RangoInicio.Trim) _
                                    & "," & IIf(IndicesBE.RangoFin.Trim = "", 0, IndicesBE.RangoFin.Trim) & ")"


        ElseIf IndicesBE.IdTipoDato = 4 Then 'TIPO FECHA
            query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,TipoIndexacion,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                             & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                             & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                             & ",'" & IndicesBE.MascaraEntrada _
                             & "','" & IndicesBE.TipoIndexacion & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) _
                             & "," & (CInt(IndicesBE.Duplicado) * -1) & "," & (CInt(IndicesBE.Obligatorio) * -1) _
                             & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

        ElseIf IndicesBE.IdTipoDato = 6 Or IndicesBE.IdTipoDato = 7 Then
            query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,IdLista,TipoIndexacion,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                             & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                             & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental & ",'" & IndicesBE.IdLista _
                             & "','" & IndicesBE.TipoIndexacion & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) _
                             & "," & (CInt(IndicesBE.Duplicado) * -1) & "," & (CInt(IndicesBE.Obligatorio) * -1) _
                             & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

        ElseIf IndicesBE.IdTipoDato = 5 Then 'Verdadero falso
            query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,TipoIndexacion,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                             & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                             & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                             & ",'" & IndicesBE.TipoIndexacion & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) _
                             & "," & (CInt(IndicesBE.Duplicado) * -1) & "," & (CInt(IndicesBE.Obligatorio) * -1) _
                             & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"
        ElseIf IndicesBE.IdTipoDato = 8 Then 'Grilla

            Dim ItemGrilla As IndiceGrillaBE
            Dim StrIndicesGrilla As String
            For Each ItemGrilla In IndicesBE.IndicesGrillaCollection
                StrIndicesGrilla += ItemGrilla.Indice & vbNewLine
            Next
            query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,TipoIndexacion,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion,IndicesGrilla)" _
                             & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                             & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                             & ",'" & IndicesBE.TipoIndexacion & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) _
                             & "," & (CInt(IndicesBE.Duplicado) * -1) & "," & (CInt(IndicesBE.Obligatorio) * -1) _
                             & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ",'" & StrIndicesGrilla & "')"

        End If

        If IndicesBE.ValueDefault Then
            If IndicesBE.Correlativy Then
                If IndicesBE.PrefijoBol Then
                    query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,Prefijo,PrefijoAdelante,CorrelativoValor," _
                        & "CorrelativoSumar,TipoIndexacion,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                            & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                            & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                            & ",'" & IndicesBE.MascaraEntrada & "','" & IndicesBE.Prefijo _
                            & "'," & (CInt(IndicesBE.PrefijoAdelante) * -1) & "," & IndicesBE.CorrelativoValor _
                            & "," & (CInt(IndicesBE.CorrelativoSumar) * -1) _
                            & ",'" & IndicesBE.TipoIndexacion _
                            & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                            & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"
                Else
                    query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,CorrelativoValor," _
                        & "CorrelativoSumar,TipoIndexacion,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion,RangoInicio,RangoFin)" _
                            & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                            & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                            & ",'" & IndicesBE.MascaraEntrada & "'" _
                            & "," & IndicesBE.CorrelativoValor _
                            & "," & (CInt(IndicesBE.CorrelativoSumar) * -1) _
                            & ",'" & IndicesBE.TipoIndexacion _
                            & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                            & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) _
                            & "," & IndicesBE.RangoInicio & "," & IndicesBE.RangoFin & ")"


                End If
            ElseIf IndicesBE.IdTipoDato = 4 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,FormatoFecha,TipoIndexacion,Prefijo," _
                    & "PrefijoAdelante,CorrelativoValor,CorrelativoSumar,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                                 & ",'" & IndicesBE.MascaraEntrada & "','" & IndicesBE.FormatoFecha _
                                 & "','" & IndicesBE.TipoIndexacion & "','" & IndicesBE.Prefijo _
                                 & "'," & (CInt(IndicesBE.PrefijoAdelante) * -1) & "," & IndicesBE.CorrelativoValor _
                                 & "," & (CInt(IndicesBE.CorrelativoSumar) * -1) & "," & (CInt(IndicesBE.ZoomAutomatico) * -1) _
                                 & "," & (CInt(IndicesBE.Duplicado) * -1) & "," & (CInt(IndicesBE.Obligatorio) * -1) _
                                 & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

            ElseIf IndicesBE.IdTipoDato = 6 Or IndicesBE.IdLista = 7 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,IdLista,TipoIndexacion,Prefijo,PrefijoAdelante,CorrelativoValor,CorrelativoSumar,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental & ",'" & IndicesBE.IdLista _
                                 & "','" & IndicesBE.TipoIndexacion & "','" & IndicesBE.Prefijo _
                                 & "'," & (CInt(IndicesBE.PrefijoAdelante) * -1) & "," & IndicesBE.CorrelativoValor _
                                 & "," & (CInt(IndicesBE.CorrelativoSumar) * -1) & "," & (CInt(IndicesBE.ZoomAutomatico) * -1) _
                                 & "," & (CInt(IndicesBE.Duplicado) * -1) & "," & (CInt(IndicesBE.Obligatorio) * -1) _
                                 & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

            ElseIf IndicesBE.IdTipoDato = 5 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MostrarComoRadio,TipoIndexacion,Prefijo,PrefijoAdelante,CorrelativoValor,CorrelativoSumar,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental & ",'"(CInt(IndicesBE.MostrarComoRadio) * -1) _
                                 & "','" & IndicesBE.TipoIndexacion & "','" & IndicesBE.Prefijo _
                                 & "'," & (CInt(IndicesBE.PrefijoAdelante) * -1) & "," & IndicesBE.CorrelativoValor _
                                 & "," & (CInt(IndicesBE.CorrelativoSumar) * -1) & "," & (CInt(IndicesBE.ZoomAutomatico) * -1) _
                                 & "," & (CInt(IndicesBE.Duplicado) * -1) & "," & (CInt(IndicesBE.Obligatorio) * -1) _
                                 & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"
            End If
        End If


        If IndicesBE.CreateIndice Then
            query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,CadenaVariables,TipoIndexacion,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                & ",'" & IndicesBE.MascaraEntrada & "','" & IndicesBE.CadenaVariables _
                & "','" & IndicesBE.TipoIndexacion & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) _
                & "," & (CInt(IndicesBE.Duplicado) * -1) & "," & (CInt(IndicesBE.Obligatorio) * -1) _
                & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

            If IndicesBE.IdTipoDato = 4 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,FormatoFecha,TipoIndexacion,CadenaVariables,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                                 & ",'" & IndicesBE.MascaraEntrada & "','" & IndicesBE.FormatoFecha _
                                 & "','" & IndicesBE.TipoIndexacion & "','" & IndicesBE.CadenaVariables _
                                 & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                                 & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

            ElseIf IndicesBE.IdTipoDato = 6 Or IndicesBE.IdLista = 7 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,IdLista,TipoIndexacion,CadenaVariables,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental & ",'" & IndicesBE.IdLista _
                                 & "','" & IndicesBE.TipoIndexacion & "','" & IndicesBE.CadenaVariables _
                                 & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                                 & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

            ElseIf IndicesBE.IdTipoDato = 5 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MostrarComoRadio,TipoIndexacion,CadenaVariables,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental & "," & (CInt(IndicesBE.MostrarComoRadio) * -1) _
                                 & ",'" & IndicesBE.TipoIndexacion & "','" & IndicesBE.CadenaVariables _
                                 & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                                 & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"
            End If

        End If

        If IndicesBE.QueryDB Then
            query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,TipoIndexacion,ConsultaSQLExterna,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental & ",'" & IndicesBE.MascaraEntrada _
                & "','" & IndicesBE.TipoIndexacion & "','" & IndicesBE.ConsultaSQLExterna _
                & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

            If IndicesBE.IdTipoDato = 4 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MascaraEntrada,FormatoFecha,TipoIndexacion,ConsultaSQLExterna,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental _
                                 & ",'" & IndicesBE.MascaraEntrada & "','" & IndicesBE.FormatoFecha _
                                 & "','" & IndicesBE.TipoIndexacion & "','" & IndicesBE.ConsultaSQLExterna _
                                 & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                                 & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

            ElseIf IndicesBE.IdTipoDato = 6 Or IndicesBE.IdLista = 7 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,IdLista,TipoIndexacion,ConsultaSQLExterna,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental & ",'" & IndicesBE.IdLista _
                                 & "','" & IndicesBE.TipoIndexacion & "','" & IndicesBE.ConsultaSQLExterna _
                                 & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                                 & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"

            ElseIf IndicesBE.IdTipoDato = 5 Then
                query = "Insert into Indices (Nombre,NombreInterno,IdTipoDato,IdTipoDocumental,MostrarComoRadio,TipoIndexacion,ConsultaSQLExterna,ZoomAutomatico,Duplicado,Obligatorio,DobleDigitacion)" _
                                 & "Values ('" & IndicesBE.Nombre & "','" & IndicesBE.NombreInterno _
                                 & "','" & IndicesBE.IdTipoDato & "'," & IndicesBE.IdTipoDocumental & "," & (CInt(IndicesBE.MostrarComoRadio) * -1) _
                                 & ",'" & IndicesBE.TipoIndexacion & "','" & IndicesBE.ConsultaSQLExterna _
                                 & "'," & (CInt(IndicesBE.ZoomAutomatico) * -1) & "," & (CInt(IndicesBE.Duplicado) * -1) _
                                 & "," & (CInt(IndicesBE.Obligatorio) * -1) & "," & (CInt(IndicesBE.DobleDigitacion) * -1) & ")"
            End If
        End If

        Return query

    End Function

    Function GetQueryIndiceGrilla(ByVal indiceGrillaBE As IndiceGrillaBE) As String
        Dim query As String

        query = "insert into IndiceGrilla (IdGrilla,IdIndice,Orden) " & _
        "values ('" & indiceGrillaBE.IdGrilla & "','" & indiceGrillaBE.Indice & "','" & indiceGrillaBE.Orden & "')"

        Return query

    End Function

    Public Function List(ByVal IdTypeDoc As Integer) As DataTable

        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)

        Dim query As String = "select Indices.*,TipoDato.Nombre as TipoDatoDes from Indices inner join " _
                        & " TipoDato on Indices.IdTipoDato = TipoDato.IdTipoDato" _
                        & " where IdTipoDocumental=" & IdTypeDoc & " Order By IdIndice" 'TODO: HardCode

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function

    Public Function List(ByVal idIndice As Int16) As DataTable

        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)

        Dim query As String = "select Indices.*,TipoDato.Nombre as TipoDatoDes from Indices inner join TipoDato on Indices.IdTipoDato = TipoDato.IdTipoDato where IdIndice= " & idIndice

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function
    Public Function ListbyName(ByVal StrIndice As String, ByVal IdTipoDocumental As Integer) As DataTable

        Dim db As Database = DatabaseFactory.CreateDatabase(stringConnection)

        Dim query As String = "select Indices.*,TipoDato.Nombre as TipoDatoDes from Indices inner join TipoDato on " _
                                & " Indices.IdTipoDato = TipoDato.IdTipoDato where IdTipoDocumental=" _
                                & IdTipoDocumental & " and INDICES.Nombre= '" & StrIndice & "'"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function

End Class
